﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using iWMS.DAL;
using CrystalDecisions.CrystalReports;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Shared;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.IO;
//20140812 SK: add in for default printer printing
using System.Drawing.Printing;
using vbReturn.classes;

namespace vbReturn
{

    public partial class Form1 : Form
    {
        //UAT
        //public string conn = "initial catalog=PACKAUS;data source=10.27.81.96;user id=sa;password=P@ssw0rd;";
        //public string connORA = "Provider=ORAOLEDB.ORACLE;Data Source=EXETEST.GCDC.KEPPELLOG.NET;User ID=wh2;Password=wh2;Unicode=True";
        //private string seeburgerPath = @"\\10.27.71.70\EDIFILES\EDITEST\WILEY_AUS\MANUALASN\";
        //private string seeBurgerID = "sysop1";
        //private string seeBurgerPassword = "Online5upport";

        //PROD
       //public string conn = "initial catalog=PACKAUS;data source=10.27.80.96;user id=sa;password=P@ssw0rd;";
       //public string connORA = "Provider=ORAOLEDB.ORACLE;Data Source=EXEPROD.GCDC.KEPPELLOG.NET;User ID=wh6;Password=wh6;Unicode=True";

       // public string conn = System.Configuration.ConfigurationManager.ConnectionStrings["PackConn"].ConnectionString;
       // public string connORA = System.Configuration.ConfigurationManager.ConnectionStrings["ORAConn"].ConnectionString;
                
        //public string sStorer = "WILEY_AUS";
        //public string sStorer = System.Configuration.ConfigurationManager.AppSettings["Storer"];
        public string sReturnID = "";
        //20140812 SK: add in for default printer printing
        private PrintDocument printDoc;
        private PrintDocument printDoc2 = new PrintDocument();

        private List<string> sAddedExternRecID = new List<string>();

        public Form1()
        {
            initForm();

        }

        private void initForm()
        {
            InitializeComponent();
            this.ControlBox = false;//hide cross button in form

            string sErrorMsg = xmlHelper.getConfig();

            if (!string.IsNullOrEmpty(sErrorMsg))
            {
                MessageBox.Show(sErrorMsg);
                return;
            }

            txtStorer.Text = clsConfig.SStorer;
            progressBar1.Visible = false;
        }

        #region button
        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtReturnID.Text.Trim()))
            {
                MessageBox.Show("Please enter either key!!!");
                return;
            }

            if (!checkIfReturnIDValid(txtReturnID.Text.Trim()))
            {
                MessageBox.Show("No such Record!!!");
                return;
            }

            showProgressBar(2000);

            BindDG(txtReturnID.Text.Trim());
           
            btnScan.Visible = true;
            //hide scanning part.
            btnMerge.Visible = false;
            PanelView.Visible = true;
            PanelScan.Visible = false;

            txtItem.Text = "";
            txtQtyscan.Text = "";
         
        }

        private void deleteAllOphanRecords()
        {

            for (int i = 0; i < sAddedExternRecID.Count(); i++)
            {
                string sErrorMsg = null;

                if (checkIfGotOrphanExternRecKey(sAddedExternRecID[i]) == true)
                {

                    MessageBox.Show("Deleting : " + sAddedExternRecID[i]);

                    deleteExternRecID(sAddedExternRecID[i], out sErrorMsg);

                    if (!string.IsNullOrEmpty(sErrorMsg))
                    {
                        MessageBox.Show(sErrorMsg);
                        return;
                    }
                    else
                    {
                        //SK 20141009 --error on this command
                        //dg.Rows.Clear();
                    }
                }
            }

        }
        //Added by SK: allow to delete a checked item only
        private void deleteSelOphanRecords()
        {

            string sErrorMsg = null;
            foreach (DataGridViewRow dr in dg.Rows)
            {

                if (dr.Cells[0].Value != null) //Cells[0] Because in cell 0th cell we have added checkbox
                {
                    MessageBox.Show("Deleting : " + dr.Cells[1].Value.ToString());
                    deleteExternRecID(dr.Cells[1].Value.ToString(), out sErrorMsg);
                    if (!string.IsNullOrEmpty(sErrorMsg))
                    {
                        MessageBox.Show(sErrorMsg);
                        return;
                    }
                    else
                    {
                        //SK 20141009 --error on this command
                        //dg.Rows.Clear();
                    }
                }
                else
                {
                    //do nothing
                }

            }
            
            


        }


        private void button1_Click(object sender, EventArgs e)
        {
            deleteAllOphanRecords();

            Dispose();

        }

        private void btnMerge_Click(object sender, EventArgs e)
        {
            
            string ExternReceiptKey="";
            //1. get return ID
            sReturnID = getRGANum();

            foreach (DataGridViewRow dr in dg.Rows)
            {
                btnScan.Visible = true;

                if (dr.Cells[0].Value != null) //Cells[0] Because in cell 0th cell we have added checkbox
                {

                    ExternReceiptKey = ExternReceiptKey + "'" + dr.Cells[1].Value.ToString() + "',";
                    
                }
                else
                {
                    //btnScan.Visible = false;
                    //MessageBox.Show("Please select at least one record!"); 
                }

            }

            if (ExternReceiptKey != "")
            {
                //remove last comma , 
                ExternReceiptKey = ExternReceiptKey.Remove(ExternReceiptKey.Length - 1);
                //Update ASN status 
                updateReturnID(ExternReceiptKey, sReturnID);

                btnMerge.Visible = false;
                btnScan.Visible = true;

            }
            else
            {
                
                //sk - 20141009
                btnMerge.Visible = true;
                btnScan.Visible = false;
                MessageBox.Show("Please select at least one record!"); 


                //btnMerge.Visible = true;
                //btnScan.Visible = false;
                //MessageBox.Show("Error getting RGA number!"); 
            }

        }

        //private checkIf

        private void btnAdd_Click(object sender, EventArgs e)
        {
            btnScan.Visible = false;
            btnMerge.Visible = true;

            string sReceiptKeyFrom = "";
            sReceiptKeyFrom = txtReceiptKeyFrom.Text.Trim();

            showProgressBar(2000);

            //1. check if order already added into RGA table
            string sReturnID = ExtractRGA(sReceiptKeyFrom);
            if (sReturnID == "")
            {


                //2. Pull in data from EXceed and insert into tables
                sReturnID = ExtractEXceed(sReceiptKeyFrom);

                if (sReturnID == "")
                {
                    sAddedExternRecID.Add(txtReceiptKeyFrom.Text.Trim());
                    txtReceiptKeyFrom.Text = "";
                    txtReceiptKeyFrom.Focus();
                    //3. display out the Receipt infor added. return ID is "", returnID will generate after click on Merge button
                    bool bGotRecord = false;
                    
                    BindDG(sReturnID);

                    /*
                    if (!bGotRecord)
                    {
                        MessageBox.Show("No Record Found!!!");
                        return;
                    }
                    */

                    btnMerge.Visible = true;
                    
 
                }
                else
                {//return no record found!
                    MessageBox.Show("Extern Receipt Key " + sReceiptKeyFrom + " not found!");
                    txtReceiptKeyFrom.Text = "";
                    txtReceiptKeyFrom.Focus();
                }

            }
            else
            {
                MessageBox.Show("Warning:" + sReceiptKeyFrom + " already found in system with RGA no:" + sReturnID);
                txtReceiptKeyFrom.Text = "";
                txtReceiptKeyFrom.Focus();
            }


        }

        private void btnScan_Click(object sender, EventArgs e)
        {
            ddlCondition.Items.Clear();
            PanelView.Visible = false;
            PanelScan.Visible = true;
            txtItem.Focus();

            //SK 20141009 
            if (sReturnID == "")
            {
                sReturnID = txtReturnID.Text.Trim();
            }

            lblscan.Text = "Scanning Item for Return ID :";
            txtReturnIDcopy.Text = sReturnID;

           
            ddlCondition.Items.Insert(0,"GOOD");
            ddlCondition.Items.Insert(1,"DAMAGE");
            ddlCondition.SelectedIndex = 0;

            showScanning(sReturnID, "");
        }

         
        private void txtItem_TextChanged(object sender, EventArgs e)
        {
            this.txtItem.KeyPress += new System.Windows.Forms.KeyPressEventHandler(CheckEnterItem);

        }

        private void txtItem_Leave(object sender, EventArgs e)
        {

        }
        private void txtQtyscan_TextChanged(object sender, EventArgs e)
        {
            
 
            this.txtQtyscan.KeyPress += new System.Windows.Forms.KeyPressEventHandler(CheckEnter);
            

        }


        //SK 20141009 comment out
        //private void btnSubmit_Click(object sender, EventArgs e)
        //{
          
        //}


        #endregion 

        #region function

        private string performTrans()
        {

            string sItem = txtItem.Text.Trim().ToUpper();
            string sCondition = ddlCondition.SelectedItem.ToString();


            string externReceiptKey = txtReceiptKeyFrom.Text;

            if (txtItem.Text != "")
            {
                if (txtQtyscan.Text != "")
                {
                    if (!Regex.Match(txtQtyscan.Text, @"^(((\d{1,3})(,\d{3})*)|(\d+))(.\d+)?$").Success)
                    {
                        //   MessageBox.Show(txtQtyscan.Text + " is Invalid number.");
                        txtQtyscan.Focus();
                    }

                    else
                    {
                        decimal QtyScan = Convert.ToDecimal(txtQtyscan.Text);
                        //update / insert data to db
                        AllocateQty(sReturnID, sItem, sCondition, QtyScan);

                        //Update receipt upload table
                        UpdateReceiptUpload(sReturnID);

                        //show scanning part.
                        showScanning(sReturnID, sItem);

                        //print label must print out when qty expexted  = qty scan? 
                        //string cartonLabelPrinterName = "EasyCoder PD42 (203 dpi) - DP on Ne04:";
                        //string binLabelPrinterName = "ZDesigner TLP 2844 on Ne01:";
                        txtItem.Text = "";
                        txtItem.Focus();
                        txtQtyscan.Text = "";
                    }


                }

            }
            else
            {
                MessageBox.Show("Invalid information entered!");
            }
            return "";
        }

        private string showScanning(string ReturnID, string SKU)
        {

            btnMerge.Visible = false;
            PanelView.Visible = false;
            PanelScan.Visible = true;
            txtItem.Focus();

            PanelSum.Visible = false;
            PanelSumDamage.Visible = false;
            PanelSumRGA.Visible = true;

            if (SKU != "")
            {
                BindDGSum(ReturnID, SKU);
                BindDGSumDamage(ReturnID, SKU);
                PanelSum.Visible = true;
                PanelSumDamage.Visible = true;

            }

            BindDGSumRGA(ReturnID);
            return "";
        }

        private Boolean BindDG(string sReturnID) 
        {
            string sCriteria = "";
            string sQuery = "";
            bool bValidate = true;
            string ExternReceiptKey = "";
            
            DataSet ds;



            try
            {
                sQuery = GetQuery(sReturnID);
                ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);

                //Create table to populate to datagrid
                DataTable dt = new DataTable("Table");

                //Add additional column name
                //  dt.Columns.Add(new DataColumn("DETAIL", typeof(string)));
                PanelView.Visible = true;
                PanelScan.Visible = false;

                //Get column name
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    dt.Columns.Add(new DataColumn(ds.Tables[0].Columns[i].ColumnName, typeof(string)));

                //Get row records
                int iCount = ds.Tables[0].Columns.Count;


                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DataRow currRow = ds.Tables[0].Rows[i];

                    //Add additional column value                  
                    DataRow row = dt.NewRow();


                    ExternReceiptKey = currRow["ExternReceiptKey"].ToString();
                    // row[0] = "<INPUT TYPE='checkbox' name='checkbox1' value='" + ExternReceiptKey + "' checked></checked>";


                    //Add rest of the Rows
                    for (int j = 0; j < iCount; j++)
                        row[j] = currRow[j].ToString();


                    dt.Rows.Add(row);
                }


                dg.DataSource = dt;

            }

            catch (Exception err)
            {
                MessageBox.Show("BindDG Error:" + err.Message);

            }

            return bValidate;

        }

        private string GetQuery(string sReturnID)
        {
            string sCriteria = "";
            string sSubJoin = "";

           
            //-----------------------------------------------------------
            //Filter Criteria
            //-----------------------------------------------------------

            sCriteria = " WHERE ReturnID = '" + sReturnID + "' " +
                       " AND ReceiptKey <> ''";

            string sFields = "distinct  ExternReceiptKey,ReceiptKey,CarrierKey,  WarehouseReference ";
            sFields = sFields + ", ASNType ";


            string sQuery = "select " + sFields +
                                    " from RGA_Det " +
                                    sSubJoin + sCriteria + " Order by ExternReceiptKey";

            return sQuery;

        }

        /// <summary>
        /// To check if a returnID is valid
        /// </summary>
        /// <param name="sReturnID"></param>
        /// <returns></returns>
        private bool checkIfReturnIDValid(string sReturnID)
        {
            string sQuery = "Select count(*) as counter from RGA_Det where storerkey = '" + clsConfig.SStorer + "' And ReturnID = '" + sReturnID + "'";
            DataSet ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);



            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow rd = ds.Tables[0].Rows[0];
                if (rd["counter"].ToString() == "0")
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            return false;

        }

        private bool checkIfGotOrphanExternRecKey(string sExterReceiptKey)
        {
            string sQuery = "Select count(*) as counter from RGA_Det where storerkey = '" + clsConfig.SStorer + "' And ExternReceiptKey = '" + sExterReceiptKey + "' AND len(ReturnID) = 0";
            DataSet ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);



            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow rd = ds.Tables[0].Rows[0];
                if (rd["counter"].ToString() == "0")
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            return false;

        }

        private bool updateReturnID(string ExternReceiptKey, string ReturnID)
        {

          
            string sQuery = "";
            bool bResult = false;
            try
            {

                sQuery = "UPDATE RGA_Det SET " +
                      " ReturnID ='" + ReturnID + "' " +
                      " WHERE ExternReceiptKey in (" + ExternReceiptKey + ")" ;

                bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);

                sQuery = "UPDATE RGA_ReceiptUpload SET " +
                      " RETURNID ='" + ReturnID + "' " +
                      " WHERE EXTERNRECEIPTKEY in (" + ExternReceiptKey + ")";

                bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);

            }
            catch (Exception err)
            {
                MessageBox.Show("UpdateReturnID Error:" + err.Message);
            }


            return bResult;

        }

        private string ExtractRGA(string ExternReceiptKey)
        {
            string sQuery = "Select Top 1 ReturnID from RGA_Det where storerkey = '" + clsConfig.SStorer + "' And ExternReceiptKey = '" + ExternReceiptKey + "'";
            DataSet ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);



            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow rd = ds.Tables[0].Rows[0];
                return rd["ReturnID"].ToString(); ;
            }
            else
            {
                return "";
            }


        }

        private string getRGANum()
        {
            string sQuery = "Select curr_value from cust_config where storerkey ='" + clsConfig.SStorer + "' And cust_config = 'RGA_RANGE'";
            DataSet ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);
            string RGANum = "";


            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow rd = ds.Tables[0].Rows[0];
                RGANum = "RGA" + rd["curr_value"].ToString();
                //increase RGA counter.
                UpdateRGANum();
            }
            return RGANum;

        }

        private string UpdateRGANum()
        {
            string sReturn = "";
            try
            {


                //save record into shipment table.

                string sQuery = "Update cust_config   " +
                    "SET curr_value =convert(varchar(10),convert(numeric(18,0), curr_value) +1)" +
                    " WHERE storerkey  ='" + clsConfig.SStorer + "' AND cust_config ='RGA_RANGE'";

                bool bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);

            }
            catch (Exception err)
            {
                MessageBox.Show("UpdateRGANumber Error:" + err.Message);

            }

            return sReturn = "";

        }

        private string ExtractEXceed(string sReceiptKeyFrom)
        {
            Boolean bResult = false;
            string sReturnID = "";
            string sQuery = "";
            string ReceiptKey = "";
            string ExternReceiptKey = "";
            string ExternLineNo = "";
            string CarrierKey = "";
            string WarehouseReference = "";
            string ASNType = "";
            string SKU = "";
            string QtyExpected = "";
            string UOM = "";
            decimal UnitPrice = 0;
            string Destruct = "";
            string LOC = "";
            string Lottable01 = "";
            string Lottable02 = "";
            string Lottable03 = "";
            string Lottable04 = "";
            string Lottable05 = "";
            string Packsize = "";
            int i = 1;
            bool bFound = false;
            try
            {
                //clear previous data first. 
                sQuery = " DELETE FROM RGA_Det where ExternReceiptKey ='" + sReceiptKeyFrom + "' AND Storerkey='" + clsConfig.SStorer + "'";
                bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);

                sQuery = " DELETE FROM RGA_ReceiptUpload where ExternReceiptKey ='" + sReceiptKeyFrom + "' AND Storerkey='" + clsConfig.SStorer + "'";
                bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);


                //sQuery = " select r.Receiptkey, r.ExternReceiptKey, rd.ExternLineNo,r.CarrierKey , r.WarehouseReference, " +
                //        " r.ASNType,rd.SKU,rd.QtyExpected,rd.UOM, nvl(s.susr2,0) UnitPrice,s.susr5 as Destruct," +
                //        " case when r.notes = 'Y' then 'WL-DAMAGE' " +
                //        " else " +
                //        "   nvl(decode(s.susr3||s.susr4,'OAD','WL-SCRAP', skuxloc.loc),'R-STAGE') " +
                //        " end  as LOC" +
                //        ", rd.Lottable01, rd.Lottable02, rd.Lottable03, rd.Lottable04, rd.Lottable05 ,p.casecnt" +
                //        " from receipt r , receiptdetail rd " +
                //        "                   left outer join skuXloc on skuXloc.sku = rd.sku" +
                //        "                   and skuxloc.storerkey ='" + clsConfig.SStorer + "' and skuXloc.locationtype = 'PICK'" +
                //        "                    inner join sku s on s.sku = rd.sku" +
                //        "                   and s.storerkey = rd.storerkey" +
                //        "                   and s.sku <> 'DUMMY'" +
                //         "                  inner join pack p on s.packkey = p.packkey " +
                //        " where r.storerkey = '" + clsConfig.SStorer + "'" +
                //        " and r.asntype = 'PLAN'" +
                //        " and r.receiptkey = rd.receiptkey" +
                //        " and r.status <> '9'" +
                //        " and r.externreceiptkey = '" + sReceiptKeyFrom + "'" +

                //        " order by r.externreceiptkey";

                //Updated by SK 20141029: if there is two pick face for same sku, Take only one pick face location 
                sQuery = " select r.Receiptkey, r.ExternReceiptKey, rd.ExternLineNo,r.CarrierKey , r.WarehouseReference, " +
                        " r.ASNType,rd.SKU,rd.QtyExpected,rd.UOM, nvl(s.susr2,0) UnitPrice,s.susr5 as Destruct," +
                        " case when r.notes = 'Y' then 'WL-DAMAGE' " +
                        " else " +
                        "   nvl(decode(s.susr3||s.susr4,'OAD','WL-SCRAP', skuxloc.loc),'R-STAGE') " +
                        " end  as LOC" +
                        ", rd.Lottable01, rd.Lottable02, rd.Lottable03, rd.Lottable04, rd.Lottable05 ,p.casecnt" +
                        " from receipt r , receiptdetail rd left outer join " +
                        " (select distinct sku, storerkey , loc from skuXloc " +
                        " where skuxloc.storerkey ='" + clsConfig.SStorer + "'  and skuXloc.locationtype = 'PICK' ) skuXloc on skuXloc.sku = rd.sku  " +
                        " and skuXloc.storerkey ='" + clsConfig.SStorer + "' " +
                        //"                   left outer join skuXloc on skuXloc.sku = rd.sku" +
                        //"                   and skuxloc.storerkey ='" + clsConfig.SStorer + "' and skuXloc.locationtype = 'PICK'" +
                        "                    inner join sku s on s.sku = rd.sku" +
                        "                   and s.storerkey = rd.storerkey" +
                        "                   and s.sku <> 'DUMMY'" +
                         "                  inner join pack p on s.packkey = p.packkey " +
                        " where r.storerkey = '" + clsConfig.SStorer + "'" +
                        " and r.asntype = 'PLAN'" +
                        " and r.receiptkey = rd.receiptkey" +
                        " and r.status <> '9'" +
                        " and r.externreceiptkey = '" + sReceiptKeyFrom + "'" +

                        " order by r.externreceiptkey";

                //DataSet ds = iWMS.Common.SQL.GetDataSet(connORA, sQuery);
                DataSet ds = SqlHelper.ExecuteDataset(clsConfig.SOracleConnectionString, System.Data.CommandType.Text, sQuery);

                foreach (DataTable table in ds.Tables)
                {

                    foreach (DataRow dr in table.Rows)
                    {
                        ReceiptKey = Convert.ToString(dr["ReceiptKey"]);
                        ExternReceiptKey = Convert.ToString(dr["ExternReceiptKey"]);
                        ExternLineNo = Convert.ToString(dr["ExternLineNo"]);
                        CarrierKey = Convert.ToString(dr["CarrierKey"]);
                        WarehouseReference = Convert.ToString(dr["WarehouseReference"]);
                        ASNType = Convert.ToString(dr["ASNType"]);
                        SKU = Convert.ToString(dr["SKU"]);
                        QtyExpected = Convert.ToString(dr["QtyExpected"]);
                        UOM = Convert.ToString(dr["UOM"]);
                        UnitPrice = Convert.ToDecimal(dr["UnitPrice"]);
                        Destruct = Convert.ToString(dr["Destruct"]);
                        LOC = Convert.ToString(dr["LOC"]);
                        Lottable01 = Convert.ToString(dr["Lottable01"]);
                        Lottable02 = Convert.ToString(dr["Lottable02"]);
                        Lottable03 = Convert.ToString(dr["Lottable03"]);
                        Lottable04 = Convert.ToString(dr["Lottable04"]);
                        Lottable05 = Convert.ToString(dr["Lottable05"]);
                        Packsize = Convert.ToString(dr["casecnt"]);

                        bFound = true;

                        if (i == 1)
                        {
                            //insert ASN upload file table header part in first loop. Subsequence not required.
                            bResult = InsertASNHeader(sReturnID, ReceiptKey, ExternReceiptKey, ASNType, CarrierKey, WarehouseReference);
                        }

                        bResult = InsertTemp(sReturnID, ReceiptKey, ExternReceiptKey, ExternLineNo, CarrierKey, WarehouseReference, ASNType, SKU, QtyExpected, UOM, UnitPrice,
                            Destruct, LOC, Lottable01, Lottable02, Lottable03, Lottable04, Lottable05, Packsize);

                        ReceiptKey = "";
                        ExternReceiptKey = "";
                        ExternLineNo = "";
                        CarrierKey = "";
                        WarehouseReference = "";
                        ASNType = "";
                        SKU = "";
                        QtyExpected = "";
                        UOM = "";
                        UnitPrice = 0;
                        Destruct = "";
                        LOC = "";
                        Lottable01 = "";
                        Lottable02 = "";
                        Lottable03 = "";
                        Lottable04 = "";
                        Lottable05 = "";
                        i++;

                    }
                }//close for each

                if (bFound == false)
                {
                    return "No Record found!";
                }
                else
                {
                    return "";
                }
            }
            catch (Exception err)
            {
                MessageBox.Show("ExtractEXceed Error:" + err.Message );
  
            }
            return "";

        }

        private Boolean InsertTemp(string sReturnID, string ReceiptKey, string ExternReceiptKey, string ExternLineNo,
            string CarrierKey, string WarehouseReference, string ASNType, string SKU, string QtyExpected,
            string UOM, decimal UnitPrice, string Destruct, string LOC,
            string Lottable01, string Lottable02, string Lottable03, string Lottable04, string Lottable05, string Packsize)
        {
            bool bResult = false;
            try
            {
                string sQuery = "";

                //insert into main RGA table, store all the Receipt info required,
                sQuery = " INSERT INTO RGA_Det (ReturnID, Storerkey,ReceiptKey, ExternReceiptKey,ExternLineNo,CarrierKey,WarehouseReference,ASNType, SKU, QtyExpected, UOM,UnitPrice,Destruct,LOC,Condition,Flag, ScanCondition,QtyScan,QtyScanDamage, QtyScanExcess, Packsize,Lottable01,Lottable02,Lottable03,Lottable04,Lottable05, CreationDate) " +
                    " Values ('" + sReturnID + "','" + clsConfig.SStorer + "','" + ReceiptKey + "','" + ExternReceiptKey + "','" + ExternLineNo + "','" + CarrierKey + "','" + WarehouseReference + "','" + ASNType + "','" +
                     SKU + "','" + QtyExpected + "','" + UOM + "','" + UnitPrice + "','" + Destruct + "','" + LOC + "','','NEW','','0','0','0', '" + Packsize + "', '" +
                     Lottable01 + "','" + Lottable02 + "','" + Lottable03 + "','" + Lottable04 + "','" + Lottable05 + "', getDate())";


                bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);


                //insert detail part.
                //WILEY Lottable05 Requirement: always set to 01/01/2013  00:00:00, else affect their picking process
                Lottable05 = "01/01/2013  00:00:00" ;

                //Updated by SK 20140908 - Qty expected always set to 0 first. Update when scan begin.
                sQuery = " INSERT INTO RGA_ReceiptUpload (RETURNID, STORER,RECEIPTKEY,EXTERNRECEIPTKEY,EXTERNLINENUMBER,CARRIERKEY,WAREHOUSEREFERENCE,ASNTYPE," +
                   " SKU,QTYEXPECTED,UOM, LOTTABLE01, LOTTABLE02, LOTTABLE03,LOTTABLE04,LOTTABLE05,TOLOC, DETAIL,CREATIONDT) " +
                   " Values ('" + sReturnID + "','" + clsConfig.SStorer + "','" + ReceiptKey + "','" + ExternReceiptKey + "','" + ExternLineNo + "','" + CarrierKey + "','" + WarehouseReference + "','" + ASNType + "','" +
                   SKU + "','0','" + UOM + "','" + Lottable01 + "','" + Lottable02 + "','" + Lottable03 + "','" + Lottable04 + "','" + Lottable05 + "','" + LOC + "','1',getdate())";

                bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);


                return bResult;


            }
            catch (Exception err)
            {
                MessageBox.Show("InsertTemp Error:" + err.Message);
                return false;


            }
        }


        private Boolean InsertASNHeader(string sReturnID, string ReceiptKey, string ExternReceiptKey, string ASNType,
           string CarrierKey, string WarehouseReference)
        {
            bool bResult = false;
            try
            {
                string sQuery = "";


                //insert to ASN upload file table, insert according to the upload file standard.
                //insert header part first

                sQuery = " INSERT INTO RGA_ReceiptUpload (RETURNID, STORER,RECEIPTKEY,EXTERNRECEIPTKEY,EXTERNLINENUMBER, CARRIERKEY,WAREHOUSEREFERENCE,ASNTYPE,QTYEXPECTED, SKU, DETAIL, CREATIONDT)" +
                    " Values ('" + sReturnID + "','" + clsConfig.SStorer + "','" + ReceiptKey + "','" + ExternReceiptKey + "','" + ASNType + "','" + WarehouseReference + "','" + WarehouseReference + "','" + ASNType + "','" + CarrierKey + "','" + WarehouseReference + "','0',getdate())";

                bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);


                return bResult;


            }
            catch (Exception err)
            {
                MessageBox.Show("InsertASNHeader Error:" + err.Message);
                return false;


            }
        }

        private string AllocateQty(string ReturnID, string SKU, string condition, decimal qtyScan)
        {

            string sQuery;
            DataSet ds;
            try
            {


                //check if alien item  
                sQuery = "SELECT SKU from RGA_Det where ReturnID ='" + ReturnID + "' and SKU = '" + SKU + "' and (ReceiptKey is not null or ReceiptKey <>'') ";
                ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);
                //Create table to populate to datagrid
                DataTable dt = new DataTable("Table");

                //Get row records
                int iCount = ds.Tables[0].Rows.Count;

                if (iCount > 0)
                {
                    StartAllocate(ReturnID, SKU, condition, qtyScan);
                }
                else//Alien item
                {
                    string flag = "ALIEN";


                    string display = flag + " Item, Confirm to add? ";
                    string Loc = "";
                    string ReceiptKey = "";
                    string externLineNo = "";
                    string ExternReceiptKey = "";

                    if (MessageBox.Show(display, "Alien Item", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        UpdateRecord("Startallocate", sReturnID, "ALIEN", qtyScan, SKU, condition, ReceiptKey, externLineNo, Loc, ExternReceiptKey, "", "", "");
                    }
                    else
                    {
                    }   



                }

            }
            catch (Exception err)
            {
                MessageBox.Show("AllocateQty Error:" + err.Message);

            }
            return "";
        }
       
        private string GetProLOC()
        {
            string locReturn = "";
            string sQuery = "";
            try
            {

                sQuery = " SELECT  Max(LOC) as LOC FROM V_PPLOCAVAILRTN " +
                        " Order by LOC Desc ";


                DataSet ds = SqlHelper.ExecuteDataset(clsConfig.SOracleConnectionString, System.Data.CommandType.Text, sQuery);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow rd = ds.Tables[0].Rows[0];

                    locReturn = Convert.ToString(rd["LOC"]).Trim().ToUpper();
                }

            }
            catch (Exception err)
            {
                MessageBox.Show("GetProLOC:" + err.Message);

            }
            return locReturn;
        }

        private string GetSKUXLOC(string SKU)
        {
            string locReturn = "";
            string sQuery = "";
            try
            {

                sQuery = " SELECT   max(loc) as LOC from skuxloc where sku ='" + SKU + "' " +
                        " AND storerkey ='" + clsConfig.SStorer + "' and locationtype = 'PICK'";


                DataSet ds = SqlHelper.ExecuteDataset(clsConfig.SOracleConnectionString, System.Data.CommandType.Text, sQuery);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow rd = ds.Tables[0].Rows[0];

                    locReturn = Convert.ToString(rd["LOC"]).Trim().ToUpper();
                }

            }
            catch (Exception err)
            {
                MessageBox.Show("GetSKUXLOC:" + err.Message);

            }
            return locReturn;
        }

        private string insertSKUxLOC(string storerkey, string sku, string loc, decimal qty)
        {
            string sQuery = "";
            try
            {
                //Updated by SK: 20140903 WILEY AUS - Qty- 0, Min = 1, max = 5000
                sQuery = "Insert into skuXloc (storerkey,sku,loc,qty,qtylocationminimum,qtylocationlimit,locationtype)" +
                     "Select '" + storerkey + "','" + sku + "','" + loc + "','" + '0' + "', '1', '5000','PICK' " +
                     " From sku  inner join pack on sku.packkey=pack.packkey " +
                     " WHERE sku ='" + sku + "' AND Storerkey='" + storerkey + "'";
                
                int i = SqlHelper.ExecuteNonQuery(clsConfig.SOracleConnectionString, System.Data.CommandType.Text, sQuery);


            }
            catch (Exception err)
            {
                MessageBox.Show("insertSKUxLOC:" + err.Message);
            }
            return "";


        }

        private string UpdateLOCFlag(string loc)
        {
            string sQuery = "";
            try
            {

                sQuery = " Update Loc SET locationtype='PICK' " +
                     " WHERE loc ='" + loc + "'";
               
                int i = SqlHelper.ExecuteNonQuery(clsConfig.SOracleConnectionString, System.Data.CommandType.Text, sQuery);


            }
            catch (Exception err)
            {
                MessageBox.Show("UpdateLOCFlag:" + err.Message);

            }
            return "";
        }
        
        private bool  UpdateRecord(string path, string returnID, string flag , decimal qtyscan, string sku,string condition, string ReceiptKey, string externLineNo,string Loc, 
            string  ExternReceiptKey, string UOM, string WarehouseReference,string CarrierKey)
        {
            

            bool bResult = true;
            string sQuery = "";

                sQuery = "";
                if (flag == "ALIEN")
                {
                    sQuery = " INSERT INTO RGA_Det (ReturnID, Storerkey,SKU, QtyExpected,QtyScanAlien,QtyScan, QtyScanDamage, QtyScanExcess, Condition, ScanCondition, CreationDate) " +
                    " Values ('" + returnID + "','" + clsConfig.SStorer + "','" + sku + "','0','" + qtyscan + "','0','0','0','" + condition + "', '" + flag + "',getdate())";
                    bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);

                }
                else if (flag == "DAMAGE" && string.IsNullOrEmpty(ReceiptKey) != true)
                {
                    

                    //update to specify receipt data which get from startallocate function with Dataset record found
                    sQuery = "UPDATE RGA_Det SET " +
                   "QtyScanDamage = ISNULL(QtyScanDamage,0) + " + qtyscan;
                   //", LOCScan = '" + Loc + "' "+
                   // ",Condition = '" + condition + "' ";
                    sQuery = sQuery + " WHERE STORERKEY ='" + clsConfig.SStorer + "'" +
                    " AND ReturnID='" + returnID + "'" +
                    " AND SKU='" + sku + "' " +
                    " AND ReceiptKey='" + ReceiptKey + "' " +
                    " AND ExternLineNo='" + externLineNo + "'";
                    bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);

                }
                else if (flag == "DAMAGE" && string.IsNullOrEmpty(ReceiptKey) == true)
                {
                    //update to any top 1 ASN with EXCESS qty which no applicable asn to fullfill
                    sQuery = "UPDATE top (1) RGA_Det SET " +
                   "QtyScanDamage = ISNULL(QtyScanDamage,0) + " + qtyscan;
                    //",Condition = '" + condition + "' ";
                    sQuery = sQuery + " WHERE STORERKEY ='" + clsConfig.SStorer + "'" +
                    " AND ReturnID='" + returnID + "'" +
                    " AND SKU='" + sku + "' ";
                    bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);
                  
                }
                else if (flag == "EXCESS" && string.IsNullOrEmpty(ReceiptKey) == true)
                {
                    //update to any top 1 ASN with EXCESS qty which no applicable asn to fullfill
                    sQuery = "UPDATE top (1) RGA_Det SET " +
                   "QtyScanExcess = ISNULL(QtyScanExcess,0) + " + qtyscan;
                    //", Condition = '" + condition + "' ";
                    sQuery = sQuery + " WHERE STORERKEY ='" + clsConfig.SStorer + "'" +
                    " AND ReturnID='" + returnID + "'" +
                    " AND SKU='" + sku + "' ";
                    bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);

                }
                else if (flag == "EXCESS" && string.IsNullOrEmpty(ReceiptKey) != true)
                {
                    //update to specify receipt data which get from startallocate function with Dataset record found
                    sQuery = "UPDATE RGA_Det SET " +
                   "QtyScanExcess = ISNULL(QtyScanExcess,0) + " + qtyscan;
                   //", LOCScan = '" + Loc + "' " +
                   // ",Condition = '" + condition + "' ";
                    sQuery = sQuery + " WHERE STORERKEY ='" + clsConfig.SStorer + "'" +
                    " AND ReturnID='" + returnID + "'" +
                    " AND SKU='" + sku + "' " +
                    " AND ReceiptKey='" + ReceiptKey + "' " +
                    " AND ExternLineNo='" + externLineNo + "'";
                    bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);

                }
                else if (flag == "NORMAL")
                {
                    //update qty scan
                    sQuery = "UPDATE RGA_Det SET " +
                    "QtyScan = ISNULL(QtyScan,0) + " + qtyscan +                 
                    ", LOCScan = '" + Loc + "' "+
                    ",Condition = '" + condition + "' ";
                    sQuery = sQuery + " WHERE STORERKEY ='" + clsConfig.SStorer + "'" +
                    " AND ReturnID='" + returnID + "'" +
                    " AND SKU='" + sku + "' " +
                    " AND ReceiptKey ='" + ReceiptKey + "'" +
                    " AND ExternLineNo='" + externLineNo + "'";
                    bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);

                }


            if (bResult)
            {
                return true;
            }
            else
            {
                MessageBox.Show("UpdateRecord error: " + sQuery);
                return false;
            }

        }
      
        private string StartAllocate(string ReturnID, string SKU, string condition, decimal qtyScan)
        {
            string Loc = "";
            string ReceiptKey = "";
            string externLineNo = "";
            string ExternReceiptKey = "";
            string sQuery = "";
            decimal oriQty = 0;
            decimal scanQty = 0;
            decimal remain = 0;
            decimal remaining = 0;
            decimal qtyUpdate = 0;
            int i = 0;
            string flag = "";
            string sProLOC = "";
            DataSet ds;
            bool loopNext = false;
            string UOM = "";
            string WarehouseReference = "";
            string CarrierKey = "";
         
            try
            {


                //need to loop thru sku

                sQuery = "SELECT ReceiptKey, ExternLineNo, ExternReceiptKey, LOC, SKU,QtyExpected," +
                    " (QtyScan+QtyScanDamage+QtyScanExcess) as TotalQtyScan, UOM,WarehouseReference,CarrierKey  from RGA_Det where ReturnID ='" + ReturnID + "' and SKU = '" + SKU + "' " +
                          " AND (QtyExpected-(QtyScan+QtyScanDamage+QtyScanExcess) ) > 0 "+
                          " ORDER BY QtyExpected desc";
                ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);
                //need to loop thru sku

                foreach (DataTable table in ds.Tables)
                {
                    if (table.Rows.Count > 0)
                    {

                        foreach (DataRow dr in table.Rows)
                        {

                            i++;
                            remaining = 0;//always reset
                            remain = 0;

                            Loc = Convert.ToString(dr["LOC"]).Trim().ToUpper();
                            ReceiptKey = Convert.ToString(dr["ReceiptKey"]).Trim();
                            externLineNo = Convert.ToString(dr["ExternLineNo"]).Trim();
                            ExternReceiptKey = Convert.ToString(dr["ExternReceiptKey"]).Trim();
                            UOM = Convert.ToString(dr["UOM"]).Trim();
                            WarehouseReference = Convert.ToString(dr["WarehouseReference"]).Trim();
                            CarrierKey = Convert.ToString(dr["CarrierKey"]).Trim();

                            oriQty = Convert.ToDecimal(dr["QtyExpected"]);
                            scanQty = Convert.ToDecimal(dr["TotalQtyScan"]);
                            
                            //check if rstage ? if yes get propose location from oracle

                            if (Loc == "R-STAGE")
                            {

                                //Updated by SK: 20140428 - Fix duplicate sku with two pick face location
                                //check skuxloc first, if record found, do not proceed to GetProLoc. 
                                sProLOC = GetSKUXLOC(SKU);

                                if (sProLOC == "")
                                {

                                    //get propose location from oracle and update flag. 
                                    sProLOC = GetProLOC();
                                    //insert to skuxloc table to occupied this location
                                    string a = insertSKUxLOC(clsConfig.SStorer, SKU, sProLOC, qtyScan);
                                    // update oracle location flag to PICK
                                    a = UpdateLOCFlag(sProLOC);
                                }
                                
                                

                            }

                            remain = oriQty - scanQty;
                            if (remain >= qtyScan)
                            {
                                qtyUpdate = qtyScan;
                                flag = "NORMAL";
                                qtyScan = 0;
                            }
                            else if (remain == 0)
                            {
                                flag = "EXCESS";
                                qtyUpdate = qtyScan;
                                qtyScan = 0;
                            }
                            else
                            {
                                flag = "NORMAL";

                                qtyUpdate = remain;
                                remaining = qtyScan - remain;
                                //still have remaining on qtyscan , need to loop next record.
                                //if reach eof record, then update as Excess qty.
                                if (i == ds.Tables[0].Rows.Count && remaining > 0)
                                {
                                    
                                    //check if user select damage condition 
                                    if (ddlCondition.SelectedItem.ToString() == "DAMAGE")
                                    {
                                        //if user select damage,except loc=WL-DAMAGE,the rest always set to WL-DAMAGE location
                                        if (Loc != "WL-SCRAP")
                                        {
                                            Loc = "WL-DAMAGE";
                                            //update normal flag one first follow by remaining later on.  
                                            UpdateRecord("Startallocate", sReturnID, "DAMAGE", qtyUpdate, SKU, "DAMAGE", ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference,CarrierKey);

                                        }
                                        else
                                        {
                                            //WL-SCRAP, condition always set to Goods
                                            //update normal flag one first follow by remaining later on.  
                                            UpdateRecord("Startallocate", sReturnID, flag, qtyUpdate, SKU, "GOOD", ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference, CarrierKey);

                                        }


                                    }
                                    else
                                    {
                                        //update normal flag one first follow by remaining later on.  
                                        UpdateRecord("Startallocate", sReturnID, "NORMAL", qtyUpdate, SKU, condition, ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference, CarrierKey);
                                        
                                    }

                                    if (ddlCondition.SelectedItem.ToString() != "DAMAGE" || Loc == "WL-SCRAP")
                                    {
                                        //print label
                                        printLabelRichText(ReturnID, clsConfig.SStorer, ReceiptKey, externLineNo, SKU, qtyUpdate);
                                        // printLabel(ReturnID, sStorer, ReceiptKey, externLineNo, SKU, qtyUpdate);
                                    }
                                    qtyUpdate = remaining;
                                    flag = "EXCESS";
                                    qtyScan = 0;

                                }
                                else
                                {
                                    // loopNext = true; //loop next record to update? 
                                    //pass on qtyScan to next row record.
                                    qtyScan = remaining;

                                }
                            }

                            //check if any propose location, if yes, update scan location to propose location
                            if (sProLOC != "")
                            {
                                Loc = sProLOC;
                            }
                            if (flag == "EXCESS")
                            {

                                string display = flag + " Item, Confirm to add? ";
                                if (MessageBox.Show(display, "EXCESS Item", MessageBoxButtons.YesNo) == DialogResult.Yes)
                                {
                                    UpdateRecord("Startallocate", sReturnID, "EXCESS", qtyUpdate, SKU, condition, ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference, CarrierKey);
                                   
                                }
                                else
                                {
                                }   

                            }
                            else if (flag == "NORMAL")
                            {

                                //check if user select damage condition 
                                if (ddlCondition.SelectedItem.ToString() == "DAMAGE")
                                {
                                    //if user select damage,except loc=WL-DAMAGE,the rest always set to WL-DAMAGE location
                                    if (Loc != "WL-SCRAP")
                                    {
                                        Loc = "WL-DAMAGE";
                                        //update normal flag one first follow by remaining later on.  
                                        UpdateRecord("Startallocate", sReturnID, "DAMAGE", qtyUpdate, SKU, "DAMAGE", ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference, CarrierKey);

                                    }
                                    else
                                    {
                                        //WL-SCRAP, condition always set to Goods
                                        //update normal flag one and print the label first follow by remaining later on as EXCESS ITEM.  
                                        UpdateRecord("Startallocate", sReturnID, flag, qtyUpdate, SKU, "GOOD", ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference, CarrierKey);
                                    }

                                }
                                else
                                {
                                    //update scan qty + remain 
                                    UpdateRecord("Startallocate", sReturnID, "NORMAL", qtyUpdate, SKU, condition, ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference, CarrierKey);

                                }

                                //get summary list by this return ID, group by SKU
                                BindDGSum(sReturnID, SKU);

                                //get damage summary info
                                BindDGSumDamage(sReturnID, SKU);

                                //get RGA summary
                                BindDGSumRGA(sReturnID);

                                if (ddlCondition.SelectedItem.ToString() != "DAMAGE" || Loc == "WL-SCRAP")
                                {
                                    //print label
                                    printLabelRichText(ReturnID, clsConfig.SStorer, ReceiptKey, externLineNo, SKU, qtyUpdate);
                                    // printLabel(ReturnID, sStorer, ReceiptKey, externLineNo, SKU, qtyUpdate);
                                }
                            }

                            //check if still need to continue loop
                            if (qtyScan == 0)
                            {
                                break;
                            }

                        }//loop for each datarow

                    }//data table nothing
                    else
                    {

                        //check if user select damage condition 

                        condition = ddlCondition.SelectedItem.ToString();


                        flag = "EXCESS";
                        string display = flag + " Item, Confirm to add ? ";

                        Loc = "";
                        ReceiptKey = "";
                        externLineNo = "";
                        ExternReceiptKey = "";
                        if (MessageBox.Show(display, "EXCESS Item", MessageBoxButtons.YesNo) == DialogResult.Yes)
                        {
                            if (condition == "DAMAGE")
                            {
                                //if user select damage,except loc=WL-DAMAGE,the rest always set to WL-DAMAGE location
                                if (Loc != "WL-SCRAP")
                                {
                                    Loc = "WL-DAMAGE";
                                    //update normal flag one first follow by remaining later on.  
                                    UpdateRecord("Startallocate", sReturnID, "DAMAGE", qtyUpdate, SKU, "DAMAGE", ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference, CarrierKey);

                                }
                                else
                                {
                                    //WL-SCRAP, condition always set to Goods
                                    //update normal flag one first follow by remaining later on.  
                                    UpdateRecord("Startallocate", sReturnID, flag, qtyUpdate, SKU, "GOOD", ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference, CarrierKey);

                                }

                            }
                            else
                            {
                                UpdateRecord("Startallocate", sReturnID, "EXCESS", qtyScan, SKU, condition, ReceiptKey, externLineNo, Loc, ExternReceiptKey, UOM, WarehouseReference, CarrierKey);

                            }

                            if (ddlCondition.SelectedItem.ToString() != "DAMAGE" || Loc == "WL-SCRAP")
                            {
                                //print label
                                printLabelRichText(ReturnID, clsConfig.SStorer, ReceiptKey, externLineNo, SKU, qtyUpdate);
                                // printLabel(ReturnID, sStorer, ReceiptKey, externLineNo, SKU, qtyUpdate);
                            }


                        }
                        else
                        {
                        }   


                    }


                }//close for each

            }
            catch (Exception err)
            {
                MessageBox.Show("StartAllocate:" + err.Message);
             
            }
            return "";
        }

        private Boolean BindDGSum(string sReturnID, string SKU)
        {
            string sCriteria = "";
            string sQuery = "";
            bool bValidate = true;
            string ExternReceiptKey = "";
            DataSet ds;


            try
            {

               
                sQuery = " SELECT SKU, isnull(Sum(QtyExpected),0) as TotalQtyExpected, isnull(Sum(QtyScan),0) as TotalGoodQty " +
                    " , isnull(Sum(QtyScanDamage),0) as TotalDamageQty" +
                    ", isnull(sum(QtyScanExcess) ,0) as TotalExcessQty   " +
                    ", isnull( Sum(QtyScan) + Sum(QtyScanDamage) + sum(QtyScanExcess), 0) as TotalQtyScan" +
                        " FROM RGA_Det " +
                        " WHERE ReturnID='" + sReturnID + "' and ScanCondition <> 'ALIEN'" +
                        " AND upper(SKU)='" + SKU + "' " +
                        " Group by SKU ";
                ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);

                //Create table to populate to datagrid
                DataTable dt = new DataTable("Table");

                PanelSum.Visible = true;


                //Get column name
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    dt.Columns.Add(new DataColumn(ds.Tables[0].Columns[i].ColumnName, typeof(string)));

                //Get row records
                int iCount = ds.Tables[0].Columns.Count;


                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DataRow currRow = ds.Tables[0].Rows[i];

                    //Add additional column value                  
                    DataRow row = dt.NewRow();

                    //Add rest of the Rows
                    for (int j = 0; j < iCount; j++)
                        row[j] = currRow[j].ToString();


                    dt.Rows.Add(row);
                }

                dgSum.DataSource = dt;
               
            }

            catch (Exception err)
            {
                MessageBox.Show("BindDGSum:" + err.Message);

            }

            return bValidate;

        }

        private Boolean BindDGSumDamage(string sReturnID, string SKU)
        {
            string sCriteria = "";
            string sQuery = "";
            bool bValidate = true;
            string ExternReceiptKey = "";
            DataSet ds;


            try
            {

             
                sQuery = " SELECT  RGA_Det.SKU, isnull(Sum(QtyExpected),0) as TotalQtyExpected" +
                    ", isnull(Sum(QtyScanDamage),0) as TotalDamageQty " +
                    ",  isnull(Sum(QtyScanDamage),0) * uPrice.UnitPrice as  TotalDamageValue " +
                    " ,case when isnull(Sum(QtyScanDamage),0) = 0 then 0 else (Sum(QtyScanDamage) / Sum(QtyExpected)) end as '%OfDamage' " +
                    " FROM RGA_Det " +
                    "Inner join (" +
                    "	 select top 1 ReturnID, SKU , UnitPrice as UnitPrice from RGA_Det " +
                    "    where ReturnID='" + sReturnID + "' AND upper(SKU)='" + SKU + "' " +
                    ") uPrice on uPrice.SKU = RGA_Det.SKU " +
                    "and uPrice.ReturnID = RGA_Det.ReturnID " +
                        " WHERE RGA_Det.ReturnID='" + sReturnID + "' " +
                        " AND upper(RGA_Det.SKU)='" + SKU + "' and ScanCondition <> 'ALIEN'" +
                        " Group by RGA_Det.SKU,uPrice.UnitPrice";
                ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);

                //Create table to populate to datagrid
                DataTable dt = new DataTable("Table");

                PanelSumDamage.Visible = true;


                //Get column name
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    dt.Columns.Add(new DataColumn(ds.Tables[0].Columns[i].ColumnName, typeof(string)));

                //Get row records
                int iCount = ds.Tables[0].Columns.Count;


                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DataRow currRow = ds.Tables[0].Rows[i];

                    //Add additional column value                  
                    DataRow row = dt.NewRow();

                    //Add rest of the Rows
                    for (int j = 0; j < iCount; j++)
                        row[j] = currRow[j].ToString();


                    dt.Rows.Add(row);
                }


                dgSumDamage.DataSource = dt;
           

            }

            catch (Exception err)
            {
                MessageBox.Show("BindDGSumDamage:" + err.Message);
            }

            return bValidate;

        }

        private Boolean BindDGSumRGA(string sReturnID)
        {
            string sCriteria = "";
            string sQuery = "";
            bool bValidate = true;
            string ExternReceiptKey = "";
            DataSet ds;


            try
            {

                sQuery = " SELECT isnull(Sum(QtyExpected),0) as TotalQtyExpected, isnull(Sum(QtyScan),0) as TotalGoodQty " +
                    " , isnull(Sum(QtyScanDamage),0) as TotalDamageQty" +
                    ", isnull(sum(QtyScanExcess) ,0) as TotalExcessQty   " +
                    ", isnull(sum(QtyScanAlien) ,0) as TotalAlienQty   " +
                    " , sum( isnull(QtyScan,0)+isnull(QtyScanDamage,0)+isnull(QtyScanExcess,0)+isnull(QtyScanAlien,0)  ) as TotalQtyScan " +
                        " FROM RGA_Det " +
                        " WHERE ReturnID='" + sReturnID + "' " +
                        " Group by ReturnID ";
                ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);

                //Create table to populate to datagrid
                DataTable dt = new DataTable("Table");

                PanelSumRGA.Visible = true;


                //Get column name
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                    dt.Columns.Add(new DataColumn(ds.Tables[0].Columns[i].ColumnName, typeof(string)));

                //Get row records
                int iCount = ds.Tables[0].Columns.Count;


                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    DataRow currRow = ds.Tables[0].Rows[i];

                    //Add additional column value                  
                    DataRow row = dt.NewRow();

                    //Add rest of the Rows
                    for (int j = 0; j < iCount; j++)
                        row[j] = currRow[j].ToString();


                    dt.Rows.Add(row);
                }


                dgSumRGA.DataSource = dt;
             
            }

            catch (Exception err)
            {
                MessageBox.Show("BindDGSumRGA:" + err.Message);

            }

            return bValidate;

        }

        private bool UpdateReceiptUpload(string returnID)
        {

            bool bResult = true;
            string sQuery = "";

            sQuery = " update  q " +
                    "set q.QTYEXPECTED = a.QtyScan" +
                    ",q.TOLOC = a.LOC" +
                    " from RGA_ReceiptUpload  q" +
                    " Inner join " +
                    " ( Select ReturnID , isnull(LOCScan,'') as LOC,  isnull((QtyScan),0) as QtyScan " +
                        " ,StorerKey,ReceiptKey,ExternReceiptKey,ExternLineNo" +
                     " from RGA_Det where RETURNID ='" + returnID + "'" +
                    " ) a on q.RETURNID = a.ReturnID" +
                    " and q.STORER = a.StorerKey" +
                    " and q.RECEIPTKEY = a.ReceiptKey " +
                    " and q.EXTERNRECEIPTKEY= a.ExternReceiptKey" +
                    " and q.EXTERNLINENUMBER = a. ExternLineNo" +
                    " where q.RETURNID ='" + returnID + "'" +
                    " and q.DETAIL = '1'";


            bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);


            if (bResult)
            {
                // lblMsg.Visible = true;
                // lblMsg.Text = "Data updated! ";
                return true;
            }
            else
            {
                MessageBox.Show("UpdateReceiptUpload Error:" + sQuery);
                return false;
            }


        }

        private string printtext()
        {

            richTextBox1.Text = " Location: 6CB123D";

            return ""; 
        }
         

        private string printLabelRichText(string ReturnID, string storerkey, string ReceiptKey, string ExternLineNo, string SKU, decimal QtyScan)
        {
            try
            {

                


                string textboxInfo = "";


                string sQuery = " SELECT ReturnID, Storerkey, ReceiptKey " +
                    ", ExternReceiptKey, ExternLineNo, SKU,UOM, LOC, (left(Packsize, Charindex('.', Packsize, 1) - 1 )) as Packsize " +
                    " , QtyExpected, '" + QtyScan + "' as QtyScan, LOCScan FROM RGA_Det" +
                    " WHERE ReturnID='" + ReturnID + "' " +
                    " AND StorerKey ='" + storerkey + "' " +
                   " AND ReceiptKey = '" + ReceiptKey + "' " +
                   " AND ExternLineNo='" + ExternLineNo + "'";
                DataSet ds = iWMS.Common.SQL.GetDataSet(clsConfig.SMSSQLConnectionString, sQuery);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow rd = ds.Tables[0].Rows[0];

                   

                    textboxInfo = "LOCATION:" + Environment.NewLine;
                    textboxInfo = textboxInfo + rd["LOCScan"].ToString().Trim() + Environment.NewLine + Environment.NewLine;
                   textboxInfo = textboxInfo + "ISBN:" + Environment.NewLine;
                    textboxInfo = textboxInfo + rd["SKU"].ToString().Trim() + Environment.NewLine + Environment.NewLine;
                    textboxInfo = textboxInfo + "PACK SIZE:" + Environment.NewLine;
                   textboxInfo = textboxInfo + rd["Packsize"].ToString().Trim() + Environment.NewLine + Environment.NewLine;
                    textboxInfo = textboxInfo + "RGA NO:" + Environment.NewLine;
                    textboxInfo = textboxInfo + rd["ExternReceiptKey"].ToString().Trim() + " " + " ";
                    textboxInfo = textboxInfo + "ID:" + " ";
                    textboxInfo = textboxInfo + rd["ReturnID"].ToString().Trim() + Environment.NewLine;
                   textboxInfo = textboxInfo + DateTime.Now.ToString();

                    richTextBox1.Text = textboxInfo;
                  
                    /*
                    Font fBold = new Font("Tahoma", 16, FontStyle.Bold);
                    richTextBox1.SelectionFont = fBold;
                    richTextBox1.SelectionColor = Color.Black;
                    richTextBox1.SelectedText = "LOCATION:" + Environment.NewLine + rd["LOC"].ToString().Trim() + Environment.NewLine + Environment.NewLine; 

                    Font fBold2 = new Font("Tahoma", 16, FontStyle.Bold);
                    richTextBox1.SelectionFont = fBold2;
                    richTextBox1.SelectionColor = Color.Black;
                    richTextBox1.SelectedText = "ISBN:" + Environment.NewLine + rd["SKU"].ToString().Trim() + Environment.NewLine + Environment.NewLine;

                    Font fBold3 = new Font("Tahoma", 10, FontStyle.Bold);
                    richTextBox1.SelectionFont = fBold2;
                    richTextBox1.SelectionColor = Color.Black;
                    richTextBox1.SelectedText = "PACK SIZE:" + Environment.NewLine + rd["Packsize"].ToString().Trim() + Environment.NewLine + Environment.NewLine;

                    Font fBold4 = new Font("Tahoma", 9, FontStyle.Bold);
                    richTextBox1.SelectionFont = fBold2;
                    richTextBox1.SelectionColor = Color.Black;
                    richTextBox1.SelectedText = "RGA NO:" + Environment.NewLine + rd["ExternReceiptKey"].ToString().Trim() + Environment.NewLine + "ID:" + rd["ReturnID"].ToString().Trim() + Environment.NewLine;
                    
                    Font fBold5 = new Font("Tahoma", 9, FontStyle.Bold);
                    richTextBox1.SelectionFont = fBold2;
                    richTextBox1.SelectionColor = Color.Black;
                    richTextBox1.SelectedText = DateTime.Now.ToString();
                    */

                    printDialog1.Document = printDocument1;

                    //20140812: comment out by SK: replace printer to default printer
                   // this.printDocument1.PrinterSettings.PrinterName = "ZDesigner TLP 2844";
                    ////printDocument1.PrinterSettings.DefaultPageSettings.PaperSize = new System.Drawing.Printing.PaperSize("a2", 20,20);
                    //this.printDocument1.Print();

                    // 20140812: updated by SK: Specify the default printer to use.
                    int i;                    

                    for (i = 0; i < PrinterSettings.InstalledPrinters.Count; i++)
                    {
                        if (printDoc2.PrinterSettings.IsDefaultPrinter)
                        {
                            this.printDocument1.Print();
                            break;
                        }
                    }

                }

                
            }

            catch (Exception err)
            {
                MessageBox.Show("printLabelRichText:" + err.Message);

            }

            return "";
        }
       
        private void CheckEnter(object sender, System.Windows.Forms.KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)13)
            {
                if (txtItem.Text != "" && txtQtyscan.Text != "")
                {
     
                      //SK 20141009 - replace to normal function call 
                    // btnSubmit.PerformClick();           
                    performTrans();

                   
                }

            }
        }

        private void CheckEnterItem(object sender, System.Windows.Forms.KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)13)
            {
                if (txtItem.Text != "")
                {
                    txtQtyscan.Text = "1";
                    txtQtyscan.Focus();
                }
                else
                {
                    MessageBox.Show("Please enter Item #.");
                    //txtItem.Focus();
                }


            }
        }
        #endregion

    

        private void printDocument1_PrintPage(object sender, System.Drawing.Printing.PrintPageEventArgs e)
        {

            string strText = this.richTextBox1.Text;
            StringReader myReader;
            myReader = new StringReader(strText);

            float linesPerPage = 0;
            float yPosition = 0;
            int count = 0;
            float leftMargin = e.MarginBounds.Left;
            float topMargin = e.MarginBounds.Top;
            string line = null;
            Font printFont = this.richTextBox1.Font;
            SolidBrush myBrush = new SolidBrush(Color.Black);
                     
            line = myReader.ReadToEnd();

            if (line != null)
            {

               yPosition = 1;
               topMargin = 0;
               leftMargin = 0;

                // draw the next line in the rich edit control
               e.Graphics.DrawString(line, printFont, myBrush, leftMargin, yPosition, new StringFormat());
                count++;
            }
          
            myBrush.Dispose();

        }

        private void btnRGAReturn_Click(object sender, EventArgs e)
        {
            frmUploadRGA childForm = new frmUploadRGA();
            childForm.Show(this);
        }

        private void txtReturnID_MouseLeave(object sender, EventArgs e)
        {
            txtReceiptKeyFrom.Enabled = true;
            txtReturnID.Enabled = true;
        }

        private void txtReceiptKeyFrom_MouseLeave(object sender, EventArgs e)
        {
            txtReceiptKeyFrom.Enabled = true;
            txtReturnID.Enabled = true;
        }

        private void btnDeleteRGADet_Click(object sender, EventArgs e)
        {
            //SK 20141009 - remove message box, delete directly.
            //DialogResult dialogResult = MessageBox.Show("Confirm?", "Delete All Ophan Records", MessageBoxButtons.YesNo);
            //if (dialogResult == DialogResult.Yes)
            //{
                //deleteAllOphanRecords();
                deleteSelOphanRecords();
                BindDG("");
          //  }
            //else if (dialogResult == DialogResult.No)
            //{
                ////do something else
            //}
            
                

            /*
            List<string> sRGADetails = new List<string>();

            foreach (DataGridViewRow row in dg.Rows)
            {
                
                if (Convert.ToBoolean(row.Cells[0].Value))
                {
                    sRGADetails.Add(row.Cells[1].Value.ToString());
                } 
            }

            if (sRGADetails.Count() == 0)
            {
                MessageBox.Show("Please select a record to delete!!!");
                return;
            }
            else
            {
                for (int i = 0; i < sRGADetails.Count(); i++)
                {
                    string sErrorMsg = null;

                    deleteExternRecID(sRGADetails[i], out sErrorMsg);

                    if (!string.IsNullOrEmpty(sErrorMsg))
                    {
                        MessageBox.Show(sErrorMsg);
                        return;
                    }
                }

            }
            */
        }

        private void deleteExternRecID(String sExternRecKey, out string sErrorMsg)
        {
            sErrorMsg = null;

            
            //clear previous data first. 
            string sQuery = " DELETE FROM RGA_Det where ExternReceiptKey ='" + sExternRecKey + "' AND Storerkey='" + clsConfig.SStorer + "'";
            //SK 20141009 - another upload table
            string sQuery2 = " DELETE FROM RGA_ReceiptUpload where ExternReceiptKey ='" + sExternRecKey + "' AND Storer='" + clsConfig.SStorer + "'";

            try
            {
                bool bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery);
                bResult = iWMS.Common.SQL.ExecuteNonQuery(clsConfig.SMSSQLConnectionString, sQuery2);
            }
            catch (Exception ex)
            {
                sErrorMsg = ex.Message;
                return;
            }
        }

        private void showProgressBar(int iValue)
        {
            progressBar1.Visible = true;
            
            int i;

            progressBar1.Minimum = 0;
            progressBar1.Maximum = iValue;

            for (i = 0; i <= iValue; i++)
            {
                progressBar1.Value = i;
            }

            progressBar1.Visible = false;
        }
                   
    }
}
